/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccess;

import BussinesLogic.Request;
import DataModel.*;
import java.sql.ResultSet;
import java.util.Date;

public class Dao_Arbitro {

    public Dao_Arbitro(){}
    
    public Request consultarArbitro(String campo, int tipoConsulta) {
        /* 
         * Si tipo de consulta es 1 la busqueda del usuario se hace por identificacion
         * Si es 2 la busqueda se realiza por username
         * Campo es el valor para realizar la busqueda                
         */
        Arbitro arbitro = new Arbitro();
        Request request = new Request();
        String sql = "";

        switch (tipoConsulta) {
            case 1:
                sql = "SELECT * FROM \"Arbitro\" WHERE \"identificacion\"='" + campo + "'";
                break;
            case 2:
                sql = "SELECT * FROM \"Arbitro\" WHERE \"username\"='" + campo + "'";
                break;
        }
        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while (rqs.next()) {
                arbitro.setIdentificacion(rqs.getString(1));
                arbitro.setUsername(rqs.getString(2));
                arbitro.setPassword(rqs.getString(3));
            }
            request.setObject(arbitro);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }

        return request;
    }
    
    public Request consultarArbitros() {
        
        Request request = new Request();
        String sql = "SELECT t1.nombre, t1.apellido FROM \"Persona\" t1,\"Arbitro\" t2 "
                + "WHERE t1.identificacion=t2.identificacion";        
        
        java.util.Vector<Arbitro> v = new java.util.Vector(1,1);
        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while (rqs.next()) {
                Arbitro arbitro = new Arbitro();
                arbitro.setNombre(rqs.getString(1));
                arbitro.setApellido(rqs.getString(2));
                v.add(arbitro);
            }
            request.setObject(v);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        
        return request;
    }
    
    public Request registrarArbitro(Arbitro arbitro){
        
        String sql= "INSERT INTO \"Arbitro\" (identificacion, username, password)"
                + " VALUES('" + arbitro.getIdentificacion() + "','" + arbitro.getUsername()
                + "','" + arbitro.getPassword() + "')";
        Request request = new Request();

        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.queryInsert(sql);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        return request;        
    }
    
    public Request asignarArbitroCompetencia(String arbitro, String competencia){
        
        String sql = "INSERT INTO \"Arbitraje\" (id_arbitro, competencia)"
                + " VALUES('" + arbitro + "','" + competencia + "')";
        
        Request request = new Request();

        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.queryInsert(sql);
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        return request;
    }
    
    public Request consultarDisponibilidad(Arbitro arbitro, Date fecha, String horaInicio,String horaFin){
        
        String sql = "SELECT COUNT(*) FROM \"Competencia\" t1, \"Arbitraje\" t2, \"Persona\" t3 "
                + "WHERE t3.nombre='" + arbitro.getNombre() + "' AND t3.apellido='" + arbitro.getApellido() + "' "
                + "AND t2.id_arbitro=t3.identificacion AND t2.competencia=t1.nombre "
                + "AND t1.fecha='" + fecha+ "' AND t1.hora>='" + horaInicio + "'"
                + "AND t1.hora<='" + horaFin + "'";
        System.out.println(sql);
        
        Request request = new Request();

        try {
            Connect conn = new Connect();
            driverPGSQL driver = new driverPGSQL(conn);
            request = driver.querySelect(sql);
            ResultSet rqs = request.getRequest();
            while(rqs.next()){
                request.setObject(rqs.getInt(1));
            }
            conn.close();
        } catch (Exception e) {
            request.setTransac(false);
            request.setMessage(e.getMessage());
        }
        return request;
    }
}
